package cs757;

import java.awt.Dimension;
import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;

import javax.swing.JFrame;
import javax.swing.JPanel;
import javax.swing.JScrollPane;
import javax.swing.JTable;
import javax.swing.table.TableColumn;


/**
 * 
 * TEST CLASS
 * 
 * This class is just used for trying out JDBC connections and learning JDBC
 * 
 * Not really part of the assignment
 * 
 */
public class Assignment3 {
	static String SERVER = "ec2-107-22-45-3.compute-1.amazonaws.com";
	static String PORT = "3306";
//	static String SERVICE = "MySQL55";
	static String SCHEMA = "757_test";  //TODO change this to match your schema name, we'll have to agree on one for the assignment
	
	static String URL = "jdbc:mysql://"+SERVER+":"+PORT+"/";	
	
	/**
	 * Attempt to connect to the database through JDBC
	 * @return
	 */
	private Connection getConnection(){
		Connection con = null;
		
		try {
			//Prompt for the userid and password
			String userid = readFromStdin("Username for " + SERVER + ": ");
			String password = readFromStdin("Password for " + userid + "@" + SERVER + ": ");
			
			Class.forName("com.mysql.jdbc.Driver");

			con = DriverManager.getConnection(URL, userid, password);

		} catch(SQLException e) {
			System.out.println("SQLException: " + e.getMessage());
			e.printStackTrace();
		}
		catch(java.lang.ClassNotFoundException e) {
			System.out.print("ClassNotFoundException: ");
			System.out.println(e.getMessage());
		}
		catch(IOException e) {
			System.out.println("IOException: " + e.getMessage());
		}
		
		return con;
	}
	
	/**
	 * Read from Stdin, this is just so we don't have
	 * to commit the database password. We'll do this better for the actual
	 * project.
	 * @return a String representing the input given
	 */
	private String readFromStdin(String prompt) throws IOException{
		System.out.print(prompt);
		
		BufferedReader in = new BufferedReader(new InputStreamReader(System.in));
		
		return in.readLine();
	}
	
	/**
	 * Perform a test query on the database using the database connection
	 * @param con
	 * @return a 2-dimensional vector of results - rows x columns, 
	 * where each row is a record and each column is that record's value for that field
	 */
	private Vector<Vector<Object>> retrieveFields(Connection con){

		String selectString =
				"SELECT *" +
				"FROM "+SCHEMA+".sailors ";  //TODO change this to match your table name, just a test table for now
		
		Vector<Vector<Object>> rowData = new Vector<Vector<Object>>();

		
		try {
			/*  Yeah, this doesn't work
			DatabaseMetaData mdata = con.getMetaData();
			ResultSet schemas = mdata.getSchemas();
			while (schemas.next()){
				System.out.println(schemas.getString("SCHEMA_NAME"));
			}
			
			ResultSet tables = mdata.getTables(null, SCHEMA, null, new String[]{"table"});
			while (tables.next()){
				System.out.println(tables.getString("TABLE_NAME"));
			}
			
			ResultSet cols = mdata.getColumns(null, SCHEMA, "sailors", null);
			ArrayList<String> columnNames = new ArrayList<String>();

			while (cols.next()){
				String cName = cols.getString("COLUMN_NAME");
			}

			 */
			
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(selectString);

			while (rs.next()) {
				Vector<Object> row = new Vector<Object>();

//				for (String colName:columnNames){
//					String cValue = rs.getString(colName);
//					row.add(cValue);
//				}

				String sid = rs.getString("sid");  //TODO change these to match your column names, just a test table for now
				String sname = rs.getString("sname");
				String rating = rs.getString("rating");
				String age = rs.getString("age");

				row.add(sid);
				row.add(sname);
				row.add(rating);
				row.add(age);

				rowData.add(row);
			}
			stmt.close();
			con.close();

		} catch(SQLException ex) {
			System.out.println(selectString);
			ex.printStackTrace();
		}

		return rowData;
	}
	
	/**
	 * Put up a GUI with the results in a table
	 * @param rowData
	 */
	private void displayResults(Vector<Vector<Object>> rowData){
		Vector<String> columnNames = new Vector<String>();
		columnNames.add("sid");  //TODO change these to match your column names, just a test table for now
		columnNames.add("sname");
		columnNames.add("rating");
		columnNames.add("age");
		
		JFrame frame = new JFrame();
		frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
		frame.setSize(1000,500);
		JPanel panel = new JPanel();	
		frame.setContentPane(panel);
		JTable table = new JTable(rowData, columnNames);	
		table.setAutoResizeMode(JTable.AUTO_RESIZE_OFF);
		TableColumn column = null;
		for (int i = 0; i < 3; i++) {
		    column = table.getColumnModel().getColumn(i);
		    column.setPreferredWidth(100); 
		}

		JScrollPane scrollPane = new JScrollPane( table );
		scrollPane.setPreferredSize(new Dimension(900,400));
		panel.add( scrollPane );
		frame.setVisible(true);

	}
	
	/**
	 * @param args
	 */
	public static void main(String[] args) {
		Assignment3 test = new Assignment3();
		
		Connection con = test.getConnection();

		if (con != null){
			System.out.println("Successfully established connection to "+URL);
			Vector<Vector<Object>> rowData = test.retrieveFields(con);
			test.displayResults(rowData);
		}
		else{
			System.out.println("Epic Fail!");
		}
	}

}
